Relational Databases For Agile Developers by Ron Ballard

Author:Ron Ballard [Ballard, Ron]
Language: eng
Format: azw3
Publisher: UNKNOWN
Published: 2017-08-30T04:00:00+00:00

Data-types for Surrogate Keys

Surrogate keys are keys made up by the system (the application or the database). They have no meaning outside the database. Surrogate keys are usually positive integers, stored in four bytes with a range from one to just over two billion. This gives more than enough unique values for most tables. For very large tables, such as transaction tables in retail or banking systems, or machine-generated data, such as telematics, you will often need larger numbers. In these cases you can use a big integer, an 8-byte number that can hold over 9,000,000,000,000,000,000. I had to look up what that is called; it's nine quintillion. It doesn't really matter; it should be big enough. Of course, if you are using Oracle, then the default is a 38-digit number. I never understood why, but it seems to work.

It is not unusual now to use UUIDs for primary keys. A UUID is a 36-character string that uses a complex combination of factors that may include the machine address, the current timestamp, random numbers and hashes of locally stored values. This is all to try to ensure unique keys in distributed systems where it is not feasible to go to a single source to get the next unused value. In fact only 32 of the characters are significant, but there are four hyphens, which are always the same and always in the same positions. A well-generated UUID is probably unique, but uniqueness cannot be guaranteed.

If you don't have a distributed database, and if the chances are vanishingly small of it ever being a distributed database, with a very, very large number of records, I would suggest that you use integers instead of UUIDs.

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.